﻿using Dapper;
using Domain.Entitys;
using Domain.Repositorys;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repositorys
{
    public class UserRepositoy : IUserRepositoy
    {
        private readonly MyDbContext myDbContext;
        string str = "Data Source=.;Initial Catalog=HR_DB;Integrated Security=True;Connect Timeout=30;Encrypt=False;";

        public UserRepositoy(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        /// <summary>
        /// 根据角色编号查询对应的1角色职位
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public UR GetUR(int id)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = $@"select u.UId,u.UName,u.UTrueName,u.UPassWord,r.RolesID,r.RolesName from [dbo].[UserRoles] ur
                                inner join [dbo].[User] u on u.UId = ur.UserID
                                inner join [dbo].[Roles] r on r.RolesID = ur.RolesID
                                where u.UId = {id}";
                return sc.QueryFirstOrDefault<UR>(sql);
            }
        }

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public async Task<User> Login(User user)
        {
            return await myDbContext.Users.FirstOrDefaultAsync(u => u.UName == user.UName && u.UPassWord == user.UPassWord);
        }

        /// <summary>
        /// 查询全部用户角色
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<UR> URFill1(int a, int b)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = $@"select u.UId,u.UName,u.UTrueName,u.UPassWord,r.RolesID,r.RolesName from [dbo].[UserRoles] ur
                                inner join [dbo].[User] u on u.UId = ur.UserID
                                inner join [dbo].[Roles] r on r.RolesID = ur.RolesID";
                return sc.Query<UR>(sql).ToList().Skip((a - 1) * b).Take(b).ToList();
            }
        }
        public int URCount()
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = $@"select u.UId,u.UName,u.UTrueName,u.UPassWord,r.RolesID,r.RolesName from [dbo].[UserRoles] ur
                                inner join [dbo].[User] u on u.UId = ur.UserID
                                inner join [dbo].[Roles] r on r.RolesID = ur.RolesID";
                return sc.Query<UR>(sql).Count();
            }
        }

        /// <summary>
        /// 根据用户ID查询角色ID
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public UserRoles UserRroesById(int uid)
        {
            return myDbContext.UserRoles.Where(u => u.UserID == uid).FirstOrDefault();
        }

        /// <summary>
        /// 修改用户角色表
        /// </summary>
        /// <param name="userRoles"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public int UserRroesUpdate(UserRoles userRoles)
        {
            myDbContext.UserRoles.Update(userRoles);
            return myDbContext.SaveChanges();
        }

        /// <summary>
        /// 查询最后一条用户数据
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public User UserDesc()
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = @"SELECT TOP 1 * FROM [dbo].[User]
                                ORDER BY UID DESC";
                return sc.QueryFirstOrDefault<User>(sql);
            }
        }

        /// <summary>
        /// 添加用户角色表
        /// </summary>
        /// <param name="userRoles"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public int UserRolesAdd(UserRoles userRoles)
        {
            myDbContext.Add(userRoles);
            return myDbContext.SaveChanges();
        }

        /// <summary>
        /// 删除角色数据
        /// </summary>
        /// <param name="roles"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public int RolesDelete(Roles roles)
        {
            myDbContext.Roles.Remove(roles);
            return myDbContext.SaveChanges();
        }

        public List<User> UsersFill()
        {
            return myDbContext.Users.ToList();
        }

        public List<UR> URFill()
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = $@"select u.UId,u.UName,u.UTrueName,u.UPassWord,r.RolesID,r.RolesName from [dbo].[UserRoles] ur
                                inner join [dbo].[User] u on u.UId = ur.UserID
                                inner join [dbo].[Roles] r on r.RolesID = ur.RolesID";
                return sc.Query<UR>(sql).ToList();
            }
        }
    }
}
